﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Collections;


namespace alphabook
{
    class Database
    {
        const String DBhost = "np-43-142.netpoint.ee";
        const String DBuser = "np39674_alpha";
        const String DBpassword = "AIBo2utS";
        const String DBdatabase = "np39674_alpha";

        private MySqlConnection conn;

        public Database()
        {
            this.Open();
        }

        public void Open()
        {
            string connStr = "server=" + DBhost + ";user=" + DBuser + ";database=" + DBdatabase + ";port=3306;password=" + DBpassword + ";";
            try
            {
                if (conn == null || !conn.Ping())
                {
                    conn = new MySqlConnection(connStr);
                    conn.Open();
                }
            }
            catch (Exception e)
            {
                PrintError(e);
            }
        }

        public ArrayList Query(String sql)
        {
            if (conn.State != System.Data.ConnectionState.Open)
            {
                this.Open();
            }

            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();

                ArrayList result = new ArrayList();
                while (rdr.Read())
                {
                    Dictionary<String, object> row = new Dictionary<String, object>();
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        row.Add(rdr.GetName(i), rdr.GetValue(i));
                    }
                    result.Add(row);
                }
                rdr.Close();
                return result;
            }
            catch (Exception e)
            {
                PrintError(e);
                return null;
            }
        }

        public int Execute(String sql)
        {
            if (conn.State != System.Data.ConnectionState.Open)
            {
                this.Open();
            }

            int rowsAffected = 0;
            try
            {
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                rowsAffected = cmd.ExecuteNonQuery();
            } catch(Exception e) {
                PrintError(e);
            }
            return rowsAffected;
        }

        public void Close()
        {
            conn.Close();
        }

        private void PrintError(Exception e)
        {
            Console.WriteLine(e.ToString());
            System.Windows.Forms.MessageBox.Show(e.ToString(), "Viga!",
            System.Windows.Forms.MessageBoxButtons.OK,
            System.Windows.Forms.MessageBoxIcon.Error);
        }
    }
}
